﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BusinessEntity;

namespace BusinessService
{
   public class DeptService
    {
       public List<Department> GetAllValid()//9d7c9355-f21b-40ef-bf10-533f29124ae1
       {
           List<Department> list;
           var sql = "select * from Department where status ='营业'  order by fullname";
           var result = SqlHelper.ExecuteDataTable(sql);
           if (result.Rows.Count > 0)
           {
               list = new List<Department>();
           }
           else
               return null;
           foreach (DataRow row in result.Rows)
           {
               var fullname = row["FullName"].ToString();
               var dept = new Department()
               {
                   DeptId = row["DeptID"].ToString(),
                   FullName =fullname.LastIndexOf("/")<0?fullname:fullname.Substring(fullname.LastIndexOf("/")+1),
                   ParentId = row["ParentID"].ToString(),
               };
               list.Add(dept);
           }
           return list;
       }

       public List<Department> GetAll(bool isRemote)
       {
           if (isRemote)
           {
              SqlHelper.IsRemoteConnection = true;
           }
           List<Department> list;
           var sql = "select * from Department  order by fullname";
           var result = SqlHelper.ExecuteDataTable(sql);
           SqlHelper.IsRemoteConnection = false;
           if (isRemote)
           {
               SqlHelper.IsRemoteConnection = false;
           }
           SqlHelper.IsRemoteConnection = false;
           if (result.Rows.Count > 0)
           {
               list = new List<Department>();
           }
           else
               return null;
           foreach (DataRow row in result.Rows)
           {
               var fullname = row["FullName"].ToString();
               var dept = new Department()
               {
                   DeptId = row["DeptID"].ToString(),
                   FullName = fullname.LastIndexOf("/") < 0 ? fullname : fullname.Substring(fullname.LastIndexOf("/")+1),
                   ParentId = row["ParentID"].ToString(),
                   Status = row["Status"].ToString()
               };
               list.Add(dept);
           }
           return list;
       }



       public void Truncate()
       {
           var sql = "delete from department";
           SqlHelper.ExecuteNonQuery(sql);
       }

       public int AddOne(Department department)
       {
           var sql = "insert into department values(@deptid,@fullname,@parentid,@status)";
         return  SqlHelper.ExecuteNonQuery(sql,
               new SqlParameter("@deptid", department.DeptId),
               new SqlParameter("@fullname", department.FullName),
               new SqlParameter("@parentid", department.ParentId),
               new SqlParameter("@status", department.Status)
               );
        }

       public List<Employee> GetEmpsByDeptId(string deptId)
       {
           SqlHelper.IsRemoteConnection = true;
           var sql = @"select  dbo.Employee.EmpID, dbo.Employee.EmpName, employee.empno, employee.DomainAccount,
dbo.Department.DeptId, dbo.Department.FullName, dbo.Position.PositionName from employeepositionrole
inner join position on dbo.EmployeePositionRole.PositionID = dbo.Position.PositionID
inner join Employee on dbo.EmployeePositionRole.EmpID = dbo.Employee.EmpID
inner join Department on dbo.Position.DeptID = dbo.Department.DeptId where Department.deptid='" + deptId + "'";
           var tb = SqlHelper.ExecuteDataTable(sql);
           SqlHelper.IsRemoteConnection = false;
           List<Employee> empNameList = null;
           if (tb.Rows.Count >= 0)
           {
               empNameList = new List<Employee>();
               foreach (DataRow row in tb.Rows)
               {
                   var emp = new Employee()
                   {
                       EmpId = row["empId"].ToString(),
                       DomainAccount = row["domainAccount"].ToString(),
                       EmpName =  row["empname"].ToString(),
                       EmpNo =   row["empno"].ToString(),
                       PositionName = row["positionname"].ToString()
                   };
                   empNameList.Add(emp);
               }
           }
           return empNameList;
       }

       public void UpdateOne(string deptId, string deptName, string parentId)
       {
           string sql = "update department set fullname='" + deptName + "',parentid='" + parentId + "' where deptid='" +
                        deptId + "'";
           SqlHelper.ExecuteNonQuery(sql);
       }

       public void RevokeDept(string deptId)
       {
           string sql = "delete department  where deptid='" + deptId + "'";
           SqlHelper.ExecuteNonQuery(sql);
       }
    }    
}        
         
         
         
         